﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Collections;
using System.Data.Common;
using BaseSet.Entity.Config;

namespace BaseSet.Logic
{
    /// <summary>
    /// SqlHelper 的摘要说明
    /// </summary>
    public class SqlHelper
    {
        static private readonly string CONN_STRING;

        /// <summary>
        /// 构造函数
        /// </summary>
        static SqlHelper()
        {
            // 首先到Cache中取，因为在系统初始化时Application中已加载了。
            CONN_STRING = (String)System.Web.HttpContext.Current.Cache.Get("DBConnectionString");
            if (CONN_STRING == null || Convert.ToString(CONN_STRING) == "")
            {
                BaseConfigInfo baseconfig = BaseConfigs.GetBaseConfig();
                CONN_STRING = baseconfig.DBConnectionString;

                // Cache起来
                System.Web.HttpContext.Current.Cache.Insert("DBConnectionString", baseconfig.DBConnectionString, null, DateTime.Now.AddHours(12), TimeSpan.Zero);

            }
            //CONN_STRING = Convert.ToString(System.Configuration.ConfigurationSettings.AppSettings["Constr"]);


        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="SQLStr"></param>
        /// <returns></returns>
        public static int ExecuteSQL(string SQLStr)
        {
            int num;
            SqlConnection conn = new SqlConnection(CONN_STRING);
            conn.Open();
            SqlCommand comm = new SqlCommand(SQLStr, conn);

            try
            {
                num = Convert.ToInt32(comm.ExecuteScalar());
                conn.Close();
                return num;
            }
            catch
            {
                conn.Close();
                return 0;
            }
        }

        //Vincent(14 January 2010): Used in Admin Pages (AdminCreateNewUser and AdminUpdDel).
        public static int ExecuteSQL(string sqlStr, List<SqlParameter> paramList, out string retMsg)
        {
            int num = 0;
            retMsg = "";
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sqlStr, conn))
                {
                    foreach (SqlParameter param in paramList)
                    {
                        comm.Parameters.Add(param);
                    }

                    try
                    {              
                        num = Convert.ToInt32(comm.ExecuteScalar());
                        comm.Parameters.Clear();
                    }
                    catch (Exception ex)
                    {
                        retMsg = ex.ToString();
                        num = -1;
                    }
                    finally
                    {
                        conn.Close();
                    }

                    return num;
                }
            }
        }
        //Vincent(15 January 2010): Used in Admin Pages (AdminRetrieve).
        static public DataSet GetDataSet(string sql, List<SqlParameter> paramList, out string retMsg)
        {
            retMsg = "";
            DataSet ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    foreach (SqlParameter param in paramList)
                    {
                        comm.Parameters.Add(param);
                    }

                    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
                    {
                        try
                        {
                            adapter.Fill(ds); ;
                        }
                        catch (Exception ex)
                        {
                            retMsg = ex.ToString();
                        }
                        finally
                        {
                            comm.Parameters.Clear();
                            conn.Close();
                        }

                        return ds;
                    }
                }
            }
        }
    
        //created by : Bahar, Ariff
        //Date created: 16 October 2009
        public static int ExecuteSQL(string sql, IDictionary<string, string> parameters)
        {
            int num;
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    foreach (KeyValuePair<string, string> values in parameters)
                    {
                        comm.Parameters.AddWithValue(values.Key, values.Value);
                    }

                    try
                    {
                        num = Convert.ToInt32(comm.ExecuteScalar());
                        comm.Parameters.Clear();
                        conn.Close();
                        return num;
                    }
                    catch
                    {
                        comm.Parameters.Clear();
                        conn.Close();
                        return 0;
                    }
                }
            }
        }

        /// <summary>
        /// 传入一个sql语句，和一组可选参数执行
        /// </summary>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="parameters"></param>
        static public void Execute(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            comm.Parameters.Add(para);
                        }
                    }
                    comm.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 执行一个sql语句，并返回一个整形数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        static public int GetScaler(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            comm.Parameters.Add(para);
                        }
                    }
                    object obj = comm.ExecuteScalar();
                    int result = Convert.ToInt32(obj);
                    return result;
                }
            }
        }

        /// <summary>
        /// 执行一个sql语句，并返回一个整形数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        static public Int64 GetScalerInt64(string sql)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    object obj = comm.ExecuteScalar();
                    Int64 result = Convert.ToInt64(obj);
                    return result;
                }
            }
        }
        /// <summary>
        /// 执行一个sql语句，并返回一个OBJECT
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        static public object GetObjScaler(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            comm.Parameters.Add(para);
                        }
                    }
                    object obj = comm.ExecuteScalar();
                    return obj;
                }
            }
        }

        /// <summary>
        /// 执行一个sql语句返回一个DataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        static public SqlDataReader GetReader(string sql, params SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(CONN_STRING);
            conn.Open();
            using (SqlCommand comm = new SqlCommand(sql, conn))
            {
                if (parameters != null && parameters.Length > 0)
                {
                    foreach (SqlParameter para in parameters)
                    {
                        comm.Parameters.Add(para);
                    }
                }
                return comm.ExecuteReader();
            }
        }

       
        /// <summary>
        /// 执行sql语句返回sql语句执行所得的数据集。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        static public DataSet GetDataSet(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            comm.Parameters.Add(para);
                        }
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        conn.Close();
                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// 分页所得的数据集。
        /// </summary>
        public static void ExecuteSQL(string sql, string TableName, int start, int PageSize, out DataSet ds)
        {
            SqlConnection conn = new SqlConnection(CONN_STRING);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            ds = new DataSet();

            try
            {
                adapter.Fill(ds, start, PageSize, TableName);
                conn.Close();
            }
            catch
            {
                ds = null;
            }


        }

        /// <summary>
        /// 分页所得的数据集。
        /// </summary>
        public static void ExecuteSQL(string sql, string TableName, int start, int PageSize, out DataSet ds,params SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(CONN_STRING);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);
            SqlDataAdapter adapter = new SqlDataAdapter(comm);

            if (parameters != null && parameters.Length > 0)
            {
                foreach (SqlParameter para in parameters)
                {
                    comm.Parameters.Add(para);
                }
            }

            ds = new DataSet();

            try
            {
                adapter.Fill(ds, start, PageSize, TableName);
                conn.Close();
            }
            catch(Exception ex)
            {
                ds = null;
            }


        }


        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="pagesize">每页大小</param>
        /// <param name="pageindex">当前页数</param>
        /// <param name="field">获取字段名</param>
        /// <param name="tablename">表名</param>
        /// <param name="orderfield">排序字段</param>
        /// <param name="taxis">排序方式,true为升序，false为降序</param>
        /// <param name="condition">满足的条件</param>
        /// <returns></returns>
        public static DataSet GetPage(int pagesize, int pageindex, string field, string tablename, string orderfield, bool taxis, string condition)
        {
            string temp;
            if (taxis)
            {
                temp = "asc";
            }
            else
            {
                temp = "desc";
            }

            string sql;
            if (pageindex == 1)
            {
                if (condition == "")
                {
                    sql = "select top " + pagesize + " " + field + " from " + tablename + " order by " + orderfield + " " + temp;
                    return GetDataSet(sql);
                }
                else
                {
                    sql = "select top " + pagesize + " " + field + " from " + tablename + " where " + condition + " order by " + orderfield + " " + temp;
                    return GetDataSet(sql);
                }
            }
            else
            {
                pageindex = (pageindex - 1) * pagesize;
                if (condition == "")
                {
                    if (taxis)
                    {
                        sql = "select top " + pagesize + " " + field + " from " + tablename + " where " + orderfield + ">all(select top " + pageindex + " " + orderfield + " from " + tablename + " order by " + orderfield + " " + temp + ") order by " + orderfield + " " + temp;
                    }
                    else
                    {
                        sql = "select top " + pagesize + " " + field + " from " + tablename + " where " + orderfield + "<all(select top " + pageindex + " " + orderfield + " from " + tablename + " order by " + orderfield + " " + temp + ") order by " + orderfield + " " + temp;
                    }
                    return GetDataSet(sql);
                }
                else
                {
                    if (taxis)
                    {
                        sql = "select top " + pagesize + " " + field + " from " + tablename + " where " + condition + " and " + orderfield + ">all(select top " + pageindex + " " + orderfield + " from " + tablename + " where " + condition + " order by " + orderfield + " " + temp + ") order by " + orderfield + " " + temp;
                    }
                    else
                    {
                        sql = "select top " + pagesize + " " + field + " from " + tablename + " where " + condition + " and " + orderfield + "<all(select top " + pageindex + " " + orderfield + " from " + tablename + " where " + condition + " order by " + orderfield + " " + temp + ") order by " + orderfield + " " + temp;
                    }
                    return GetDataSet(sql);
                }
            }

        }

        /// <summary>
        /// 分页sql
        /// </summary>
        /// <param name="pagesize">每页大小</param>
        /// <param name="pageindex">当前页数</param>
        /// <param name="field">获取字段名</param>
        /// <param name="field1">不带前缀的字段名</param>
        /// <param name="tablename">表名</param>
        /// <param name="orderfield">排序字段名称</param>
        /// <param name="orderfield">不带前缀的排序字段名</param>
        /// <param name="taxis">排序方式,true为升序，false为降序</param>
        /// <param name="condition">满足的条件</param>
        /// <param name="distinct">去重=distinct，不需要=""</param>
        /// <returns>sql</returns>
        public static string GetPageSql(int pagesize, int pageindex, string field, string fieldNoPrefix, string tablename, string orderfield, string orderFieldNoPrefix, bool taxis, string condition, string distinct)
        {
            //排序方式
            string temp;
            string temp1;
            if (taxis)
            {
                //升序
                temp = "asc";
                temp1 = "desc";
            }
            else
            {
                //降序
                temp = "desc";
                temp1 = "asc";
            }

            //存储Sql语句
            string sql = string.Empty;

            //条件语句是否为空，不为空添加where
            if (!string.IsNullOrEmpty(condition))
            {
                //为条件语句添加 where
                condition = " where " + condition;
            }

            //得到当前页之前(包括当前页)的所有记录
            sql = "select " + distinct + " top " + (pagesize * pageindex).ToString() +" " + field + " from " + tablename + condition + " order by " + orderfield + " " + temp;
           
            //是否查询的是第一页
            if (pageindex > 1)
            {
                //倒序排列，取得当前页记录
                sql = "select  top " + pagesize + " " + fieldNoPrefix + " from (" + sql + ") a order by " + orderFieldNoPrefix + " " + temp1;

            }
          
            return sql;

        }

        /// <summary>
        /// 利用事务执行Sql语句
        /// </summary>
        public static bool RunSqlTransaction(string[] sql_str)
        {
            return RunSqlTransaction(sql_str, 30);
        }

        /// <summary>
        /// 利用事务执行Sql语句
        /// </summary>
        /// <param name="loadCustomTimeout">Use application configured timeout</param>
        //public static bool RunSqlTransaction(string[] sql_str, bool loadCustomTimeout)
        //{
        //    return RunSqlTransaction(sql_str, UploadTransactionTimeout);
        //}

        /// <summary>
        /// 利用事务执行Sql语句
        /// </summary>
        /// <param name="timeout">Timeout value</param>
        public static bool RunSqlTransaction(string[] sql_str, int timeout)
        {
            bool isSuccess = false;
            SqlConnection myConnection = new SqlConnection(CONN_STRING);

            // 初始化
            myConnection.Open();
            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;

            if(timeout > 30)
            {
                myCommand.CommandTimeout = timeout;
            }

            // 开启
            myTrans = myConnection.BeginTransaction();

            // Both Transaction object and transaction object
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;
            try
            {
                //myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";     
                //myCommand.ExecuteNonQuery();     
                //myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";    
                //myCommand.ExecuteNonQuery();
                for (int i = 0; i < sql_str.Length; i++)
                {
                    if (sql_str[i] != "")
                    {
                        myCommand.CommandText = sql_str[i];
                        myCommand.ExecuteNonQuery();
                    }
                }

                myTrans.Commit();
                isSuccess = true;
                // 成功
            }
            catch (Exception ex)
            {
                ex.ToString();
                try
                {
                    myTrans.Rollback();
                }

                catch (SqlException exx)
                {
                    exx.ToString();
                    //error
                }

            }
            finally
            {
                myConnection.Close();
            }
            return isSuccess;
        }

        /// <summary>
        /// 利用事务执行Sql语句
        /// </summary>
        public static int RunSqlTranByArray(string[] sql_str)
        {

            int returnValue = 0;
            int i = 0;
            SqlConnection myConnection = new SqlConnection(CONN_STRING);

            // 初始化
            myConnection.Open();
            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;

            // 开启
            myTrans = myConnection.BeginTransaction();

            // Both Transaction object and transaction object
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;
            try
            {
                //myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";     
                //myCommand.ExecuteNonQuery();     
                //myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";    
                //myCommand.ExecuteNonQuery();
                for (i = 0; i < sql_str.Length; i++)
                {
                    if (sql_str[i] != null && sql_str[i] != "")
                    {
                        myCommand.CommandText = sql_str[i];
                        myCommand.ExecuteNonQuery();
                    }
                }

                myTrans.Commit();
                // 成功
            }
            catch (Exception ex)
            {
                returnValue = i + 1;
                ex.ToString();
                try
                {
                    myTrans.Rollback();
                }

                catch (SqlException exx)
                {
                    exx.ToString();
                    //error
                }

            }
            finally
            {
                myConnection.Close();
            }

            return returnValue;
        }



        /// <summary>
        /// 执行sql语句返回sql语句执行所得的数据集。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        static public DataSet GetDataSet(string sql, string tableName, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    if (parameters != null && parameters.Length > 0)
                    {
                        foreach (SqlParameter para in parameters)
                        {
                            comm.Parameters.Add(para);
                        }
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds, tableName);
                        conn.Close();
                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// 得到Bool值
        /// </summary>
        /// <param name="SQLStr"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public static bool GetBoolValue(string SQLStr, string TableName)
        {
            DataSet ds;
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                try
                {
                    ds = new DataSet();
                    SqlDataAdapter adapter = new SqlDataAdapter(SQLStr, conn);
                    adapter.Fill(ds, TableName);
                    return true;
                }
                catch
                {
                    ds = null;
                    return false;
                }
            }
        }

        /// <summary>
        /// 将DataSet更新到数据库中
        /// </summary>
        public static bool UpdateDataSet(DataSet ds, string DataSetTableName, string[] field)
        {

            Boolean rr = true;
            SqlConnection con = new SqlConnection(CONN_STRING);
            con.Open();

            try
            {
                // ds中的字段必须与此处SQL串中的对应
                string sele_str = "select top 0 ";
                for (int i = 0; i < field.Length; i++)
                {
                    if (i != field.Length - 1)
                    {
                        sele_str += field[i] + ",";
                    }
                    else
                    {
                        sele_str += field[i];
                    }
                }

                sele_str += " from " + DataSetTableName;

                SqlCommand comm = new SqlCommand(sele_str, con);

                DataSet tempds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = comm;
                sda.Fill(tempds, DataSetTableName);

                SqlCommandBuilder scb = new SqlCommandBuilder(sda);
                tempds.Clear();
                tempds = ds.Copy();
                sda.Update(tempds, DataSetTableName);
                //sda.Update(tempds);

            }
            catch
            {
                rr = false;

            }

            con.Close();

            return rr;


        }


        #region NewCode
        //Created by: Ariff bahar
        //Date Created: 4th January 2010


        /// <summary>
        /// Use this code to replace GetScaler 
        /// </summary>
        /// <param name="sql">Sql Command</param>
        /// <param name="paraList">List of SqlParameter</param>
        /// <returns></returns>
        static public int GetScalerNew(string sql, List<SqlParameter> paraList)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    foreach (SqlParameter param in paraList)
                    {                       
                        comm.Parameters.Add(param);
                    }

                    object obj = comm.ExecuteScalar();
                    //object obj = comm.ExecuteReader();
                    int result = Convert.ToInt32(obj);
                    comm.Parameters.Clear();
                    return result;
                }
            }
        }

        /// <summary>
        /// Use this code to replace GetObjScaler 
        /// </summary>
        /// <param name="sql">Sql Command</param>
        /// <param name="paraList">List of SqlParameter</param>
        /// <returns>Object from the sql</returns>
        static public object GetObjScalerNew(string sql, List<SqlParameter> paraList)
        {
            
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    foreach (SqlParameter param in paraList)
                    {
                        comm.Parameters.Add(param);
                    }
                    object obj = comm.ExecuteScalar();
                    comm.Parameters.Clear();  
                    //conn.Close(); 
                    return obj;
                }
            }
        }

        /// <summary>
        /// Creates a new SQl parameter
        /// </summary>
        /// <param name="parameterName">Name of parameter</param>
        /// <param name="value">Value for the parameter</param>
        /// <param name="dataType">SQL Data type for the parameter</param>
        /// <returns></returns>
        static public SqlParameter CreateSqlParam(string parameterName, object value, SqlDbType dataType)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = parameterName;
            param.Value = value;
            param.SqlDbType = dataType;

            return param;
        }

        /// <summary>
        /// Use this code to replace Execute
        /// </summary>
        /// <param name="sql">Sql Command</param>
        /// <param name="paraList">List of SqlParameter</param>
        static public void ExecuteNew(string sql, List<SqlParameter> paraList)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    foreach (SqlParameter param in paraList)
                    {
                        comm.Parameters.Add(param);
                    }

                    comm.ExecuteNonQuery();
                    comm.Parameters.Clear();
                    conn.Close();
                }
            }
        }

        /// <summary>
        ///  Use this code to replace GetDataSet
        /// </summary>
        /// <param name="sql">Sql Command</param>
        /// <param name="paraList">List of SqlParameter</param>
        /// <returns>Returns a data set</returns>
        static public DataSet GetDataSetNew(string sql, List<SqlParameter> paraList)
        {
            /// Forward to overloaded version; use default timeout value
            return GetDataSetNew(sql, paraList, 30);
        }

        /// <summary>
        ///  Use this code to replace GetDataSet
        /// </summary>
        /// <param name="sql">Sql Command</param>
        /// <param name="paraList">List of SqlParameter</param>
        /// <param name="loadCustomTimeout">Use application configured timeout values</param>
        /// <returns>Returns a data set</returns>
        //static public DataSet GetDataSetNew(string sql, List<SqlParameter> paraList, bool loadCustomTimeout)
        //{
        //    /// Forward to overloaded version; use default timeout value
        //    return GetDataSetNew(sql, paraList, UploadCommandTimeout);
        //}

        /// <summary>
        /// Overloaded with customizable command timeout value
        /// </summary>
        /// <param name="sql">SQL query string</param>
        /// <param name="paraList">Parameter list</param>
        /// <param name="timeout">Timeout value (in seconds)</param>
        /// <returns>Result data set</returns>
        static public DataSet GetDataSetNew(string sql, List<SqlParameter> paraList, int timeout)
        {
            // Safety implementation; Always assume default timeout 30 is safest value. Only permit customization
            // for extended timeout.
            // Note: 0 implies infinite wait - We are disallowing this
            if(timeout < 30)
            {
                timeout = 30;
            }

            using(SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using(SqlCommand comm = new SqlCommand(sql, conn))
                {
                    comm.CommandTimeout = timeout;

                    foreach(SqlParameter param in paraList)
                    {
                        comm.Parameters.Add(param);
                    }

                    using(SqlDataAdapter adapter = new SqlDataAdapter(comm))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);
                        comm.Parameters.Clear();
                        conn.Close();
                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// Use this code to replace GetDataSet
        /// </summary>
        /// <param name="sql">Sql Command</param>
        /// <param name="tableName">Table name</param>
        /// <param name="paraList">List of SqlParameter</param>
        ///  <returns>Returns a data set</returns>
        static public DataSet GetDataSetNew(string sql, string tableName, List<SqlParameter> paraList)
        {
            using (SqlConnection conn = new SqlConnection(CONN_STRING))
            {
                conn.Open();
                using (SqlCommand comm = new SqlCommand(sql, conn))
                {
                    foreach (SqlParameter param in paraList)
                    {
                        comm.Parameters.Add(param);
                    }

                    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
                    {
                        DataSet ds = new DataSet();
                        adapter.Fill(ds, tableName);
                        comm.Parameters.Clear();
                        conn.Close();
                        return ds;
                    }
                }
            }
        }

        /// <summary>
        /// Use this code to replace ExecuteSQl
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="TableName"></param>
        /// <param name="start"></param>
        /// <param name="PageSize"></param>
        /// <param name="paramList"></param>
        /// <param name="ds"></param>
        public static void ExecuteSQLNew(string sql, string TableName, int start, int PageSize, List<SqlParameter> paramList, out DataSet ds)
        {
            SqlConnection conn = new SqlConnection(CONN_STRING);
            conn.Open();
            SqlCommand comm = new SqlCommand(sql, conn);

            foreach (SqlParameter param in paramList)
            {
                comm.Parameters.Add(param);
            }

            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            ds = new DataSet();

            try
            {
                adapter.Fill(ds, start, PageSize, TableName);
                comm.Parameters.Clear();
                conn.Close();
            }
            catch
            {
                comm.Parameters.Clear();
                ds = null;
            }
        }

        /// <summary>
        /// Convert string value to work in clause SQL statement
        /// </summary>
        /// <param name="value">value to convert</param>
        /// <param name="paramList">sqlparameter list</param>
        /// <param name="paramQuery">query for in clause</param>
        public static void getParamAndQuery(string value, out List<SqlParameter> paramList, out string paramQuery)
        {
            string[] values = value.Split(',');
            int count = values.Length;
            paramQuery = string.Empty;
            paramList = new List<SqlParameter>();

            for (int i = 0; i < count; i++)
            {
                paramQuery += "@value" + i;

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@value" + i;
                param.Value = values[i];
                paramList.Add(param);

                if (i < count - 1)
                {
                    paramQuery += ",";
                }
            }
        }

        /// <summary>
        /// Single sql with multiple parameter list
        /// </summary>
        /// <param name="collection">single SQL statement</param>
        /// <param name="collectionValue">multiple SQL paramater list</param>
        public static bool RunSqlTransactionNew(string collection, List<List<SqlParameter>> collectionValue)
        {

            bool isSuccess = false;
            SqlConnection myConnection = new SqlConnection(CONN_STRING);
            myConnection.Open();

            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;

            myTrans = myConnection.BeginTransaction();
            myCommand.Connection = myConnection;

            myCommand.Transaction = myTrans;
            try
            {
                if (!collection.Equals(string.Empty))
                {
                    myCommand.CommandText = collection;
                    int value = collectionValue.Count;

                    for (int i = 0; i < value; i++)
                    {
                        List<SqlParameter> listString = collectionValue[i];
                        myCommand.Parameters.Clear();
                        foreach (SqlParameter param in listString)
                        {
                            myCommand.Parameters.Add(param);
                        }
                        myCommand.ExecuteNonQuery();
                    }
                    myTrans.Commit();
                    myCommand.Parameters.Clear();
                    isSuccess = true;
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
                try
                {
                    myTrans.Rollback();
                    myCommand.Parameters.Clear();
                }

                catch (SqlException exx)
                {
                    exx.ToString();
                    myCommand.Parameters.Clear();   
                }
            }
            finally
            {
                myConnection.Close();
            }
            return isSuccess;

        }

        /// <summary>
        ///<summary>
        /// Multiple sql with multiple paramater list
        ///</summary>
        ///<param name="collection">collection of sql command</param>
        ///<param name="collectionValue">collection of List of SQL parameter</param>
        public static bool RunSqlTransactionNew(StringCollection collection, List<List<SqlParameter>> collectionValue)
        {

            bool isSuccess = false;
            SqlConnection myConnection = new SqlConnection(CONN_STRING);
            myConnection.Open();

            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;

            myTrans = myConnection.BeginTransaction();
            myCommand.Connection = myConnection;

            myCommand.Transaction = myTrans;
            try
            {
                int value = collection.Count;
                for (int i = 0; i < value; i++)
                {
                    myCommand.CommandText = collection[i];
                    myCommand.Parameters.Clear();
                    List<SqlParameter> listString = collectionValue[i];
                    foreach (SqlParameter param in listString)
                    {
                        myCommand.Parameters.Add(param);
                    }
                    myCommand.ExecuteNonQuery();
                }

                myTrans.Commit();
                myCommand.Parameters.Clear();
                isSuccess = true;
            }
            catch (Exception ex)
            {
                ex.ToString();
                try
                {
                    myTrans.Rollback();
                    myCommand.Parameters.Clear();
                }

                catch (SqlException exx)
                {
                    exx.ToString();
                    myCommand.Parameters.Clear();
                }
            }
            finally
            {
                myConnection.Close();
            }
            return isSuccess;

        }

        /// <summary>
        /// Multiple sql that uses single parameter list
        /// </summary>
        /// <param name="collection">collection of sql command</param>
        /// <param name="collectionValue">single list of SQL parameter</param>
        /// <returns></returns>
        public static bool RunSqlTransactionNew(StringCollection collection, List<SqlParameter> collectionValue)
        {

            bool isSuccess = false;
            SqlConnection myConnection = new SqlConnection(CONN_STRING);
            myConnection.Open();

            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;

            myTrans = myConnection.BeginTransaction();
            myCommand.Connection = myConnection;

            myCommand.Transaction = myTrans;
            try
            {
                int value = collection.Count;
                for (int i = 0; i < value; i++)
                {
                    myCommand.CommandText = collection[i];
                    myCommand.Parameters.Clear();
                    foreach (SqlParameter param in collectionValue)
                    {
                        myCommand.Parameters.Add(param);
                    }
                    myCommand.ExecuteNonQuery();
                }

                myTrans.Commit();
                myCommand.Parameters.Clear();
                isSuccess = true;
            }
            catch (Exception ex)
            {
                ex.ToString();
                try
                {
                    myTrans.Rollback();
                    myCommand.Parameters.Clear();
                }

                catch (SqlException exx)
                {
                    exx.ToString();
                    myCommand.Parameters.Clear();
                }
                throw; 
            }
            finally
            {
                myConnection.Close();
            }
            return isSuccess;

        }


        /// <summary>
        /// Multiple sql with multiple paramater list
        /// </summary>
        ///<param name="collection">collection of sql command</param>
        ///<param name="collectionValue">collection of List of SQL parameter</param>
        /// <returns></returns>
        public static int RunSqlTranByArrayNew(StringCollection collection, List<List<SqlParameter>> collectionValue)
        {

            int returnValue = 0;
            int i = 0;
            SqlConnection myConnection = new SqlConnection(CONN_STRING);

            myConnection.Open();
            SqlCommand myCommand = myConnection.CreateCommand();
            SqlTransaction myTrans;

            myTrans = myConnection.BeginTransaction();

            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;
            try
            {
                int value = collection.Count;
                for (int q = 0; q < value; q++)
                {
                    myCommand.CommandText = collection[q];
                    myCommand.Parameters.Clear();
                    List<SqlParameter> listString = collectionValue[q];
                    foreach (SqlParameter param in listString)
                    {
                        myCommand.Parameters.Add(param);
                    }
                    myCommand.ExecuteNonQuery();
                }

                myTrans.Commit();
                myCommand.Parameters.Clear();

            }
            catch (Exception ex)
            {
                returnValue = i + 1;
                ex.ToString();
                try
                {
                    myTrans.Rollback();
                    myCommand.Parameters.Clear();
                }

                catch (SqlException exx)
                {
                    exx.ToString();
                    //error
                }
                throw; 

            }
            finally
            {
                myConnection.Close();
            }

            return returnValue;
        }

        /// <summary>
        /// To combine parameter list 1 and 2, and to change query 1
        /// Use this when there are two in clause with different values
        /// </summary>
        /// <param name="list1">Parameter List 1</param>
        /// <param name="query1">query for list 1</param>
        /// <param name="list2">Parameter List 2</param>
        /// <param name="completeList">New parameterlist</param>
        /// <param name="newQuery1">New query for list 1</param>
        public static void JoinParamAndQuery(List<SqlParameter> list1, string query1, List<SqlParameter> list2,out List<SqlParameter> completeList, out string newQuery1)
        {
            foreach (SqlParameter items in list1)
            {
                //string newName = items.ParameterName.Replace("@value", "@value1");
                // Added by Konguvel for TD 65633 to avoid the same Parameter name declaration with list1 & list2
                string newName = items.ParameterName.Replace("@value", "@value_");
                // Code ends here for avoiding same parameter
                SqlParameter param = new SqlParameter();
                param.ParameterName = newName;
                param.Value = items.Value;

                list2.Add(param);
            }
            //Bahar, Ariff (5th May 2010) Code fix
            //newQuery1 = query1.Replace("@item", "@item1");

            // Updated by Konguvel for TD 65633 to rename the new query string as we did for the list1
            newQuery1 = query1.Replace("@value", "@value_");
            completeList = list2;
        }

        //public static bool BulkUploadTransaction(DataTable dtSell, String tableName)
        //{
        //    SqlConnection myConnection = new SqlConnection(CONN_STRING);
        //    SqlBulkCopy bulkCopy = new SqlBulkCopy(myConnection.ConnectionString, SqlBulkCopyOptions.FireTriggers);
        //    bulkCopy.DestinationTableName = tableName;
        //    bulkCopy.BulkCopyTimeout = Convert.ToInt32(ConfigurationManager.AppSettings["BulkCopyTimeout"]);
        //    bulkCopy.BatchSize = Convert.ToInt32(ConfigurationManager.AppSettings["BatchSize"]);
        //    try
        //    {
        //        //Fetch all rows in excel into database table with name databaseTableName
        //        bulkCopy.WriteToServer(dtSell);
        //    }
        //    catch(Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        myConnection.Close();
        //        bulkCopy.Close();
        //    }
        //    return true;
        //}

        //Vincent (24 April 2010): Use to verify the relations between uploaded ServiceTag and Store. 
        public static string IsStoreUploadedServiceTagS(string storeId, string serviceTag, string loginUserId)
        {
            string result = "";
            SqlConnection myConnection = new SqlConnection(CONN_STRING);
            try
            {
                myConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "SP_IsStoreUploadedServiceTagS";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = myConnection;

                SqlParameter inparm1 = cmd.Parameters.Add("StoreID", SqlDbType.VarChar, 10);
                inparm1.Direction = ParameterDirection.Input;
                inparm1.Value = storeId;

                SqlParameter inparm2 = cmd.Parameters.Add("ServiceTag", SqlDbType.VarChar, 20);
                inparm2.Direction = ParameterDirection.Input;
                inparm2.Value = serviceTag;

                SqlParameter inparm3 = cmd.Parameters.Add("UserID", SqlDbType.VarChar, 10);
                inparm3.Direction = ParameterDirection.Input;
                inparm3.Value = loginUserId;

                SqlParameter ouparm = cmd.Parameters.Add("@RetError", SqlDbType.VarChar, 200);
                ouparm.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                result = cmd.Parameters["@RetError"].Value.ToString();
            }
            catch (Exception e)
            {
                result = "Error encountered: " + e.Message;
            }
            finally
            {
                if (myConnection != null)
                {
                    myConnection.Close();
                    myConnection = null;
                }
            }

            return result;
        }

        #endregion


    }


}

